--CREATE EXTENSION pgcrypto;

-- 帐号
DROP TABLE IF EXISTS account CASCADE;
CREATE TABLE account
(
    id            SERIAL8      NOT NULL PRIMARY KEY,
    loginName     VARCHAR(20)  NOT NULL,
    name          VARCHAR(20)  NOT NULL,
    password      VARCHAR(60)  NOT NULL,
    phoneNo       VARCHAR(15)  NOT NULL,
    role          VARCHAR(20)  NOT NULL,
    avatar        VARCHAR(50)  NULL,
    activated     BOOLEAN      NOT NULL DEFAULT FALSE,
    enabled       BOOLEAN      NOT NULL DEFAULT TRUE,
    remarks       VARCHAR(500) NULL,
    createTime    TIMESTAMP    NOT NULL DEFAULT now(),
    updateTime    TIMESTAMP    NOT NULL DEFAULT now(),
    createBy      INT          NOT NULL DEFAULT 0,
    updateBy      INT          NOT NULL DEFAULT 0,
    lastLoginTime TIMESTAMP    NULL,
    lastLoginIp   VARCHAR(50)  NULL,
    UNIQUE (loginName, phoneNo)
);

COMMENT ON TABLE account IS '用户(包括管理员和业务员)';
COMMENT ON COLUMN account.id IS 'id(自增)';
COMMENT ON COLUMN account.loginName IS '登录名';
COMMENT ON COLUMN account.name IS '用户姓名';
COMMENT ON COLUMN account.password IS '密码(加密)';
COMMENT ON COLUMN account.phoneNo IS '国内手机号码';
COMMENT ON COLUMN account.role IS '角色(超管/管理员/业务员,因为角色单一,暂使用字段记录)';
COMMENT ON COLUMN account.activated IS '是否激活(即是否修改过默认密码)';
COMMENT ON COLUMN account.enabled IS '是否启用(禁用帐号不能登录)';
COMMENT ON COLUMN account.avatar IS '头像';
COMMENT ON COLUMN account.remarks IS '备注';
COMMENT ON COLUMN account.createTime IS '记录创建时间';
COMMENT ON COLUMN account.updateTime IS '记录最后一次更新时间';
COMMENT ON COLUMN account.createBy IS '添加帐号的用户';
COMMENT ON COLUMN account.updateBy IS '最近修改帐号的用户';
COMMENT ON COLUMN account.lastLoginTime IS '最近一次登录时间';
COMMENT ON COLUMN account.lastLoginIp IS '最近一次登录IP';

INSERT INTO account (loginName, name, password, phoneNo, role, avatar, remarks)
VALUES ('admin', '超级管理员', '$2a$12$2Otc0P522cYcsJKmTrih4eHytpltxSdeV1yCt4LlMjTQeuFPTbX/O', '13312344321', 'super_admin',
        'a026-man-2.svg', '超级管理员,系统初始创建');


DROP TABLE IF EXISTS canceled_account CASCADE;
CREATE TABLE canceled_account
(
    id            SERIAL8      NOT NULL PRIMARY KEY,
    accountId     INT8         NOT NULL,
    loginName     VARCHAR(20)  NOT NULL,
    name          VARCHAR(20)  NOT NULL,
    password      VARCHAR(60)  NOT NULL,
    phoneNo       VARCHAR(15)  NOT NULL,
    role          VARCHAR(20)  NOT NULL,
    avatar        VARCHAR(50)  NULL,
    activated     BOOLEAN      NOT NULL DEFAULT FALSE,
    enabled       BOOLEAN      NOT NULL DEFAULT TRUE,
    remarks       VARCHAR(500) NULL,
    createTime    TIMESTAMP    NOT NULL DEFAULT now(),
    updateTime    TIMESTAMP    NOT NULL DEFAULT now(),
    createBy      INT          NOT NULL DEFAULT 0,
    updateBy      INT          NOT NULL DEFAULT 0,
    lastLoginTime TIMESTAMP    NULL,
    lastLoginIp   VARCHAR(50)  NULL,
    deleteTime    TIMESTAMP    NULL,
    UNIQUE (loginName, phoneNo)
);

COMMENT ON TABLE canceled_account IS '已经被注销的用户';
COMMENT ON COLUMN canceled_account.accountId IS '已经被注销的用户id';
COMMENT ON COLUMN canceled_account.deleteTime IS '被注销的时间';


CREATE OR REPLACE FUNCTION save_canceled_account() RETURNS TRIGGER AS
$$
BEGIN
    INSERT INTO canceled_account (accountId, loginName, name, password, phoneNo, role, avatar, activated, enabled,
                                  remarks, createTime, updateTime, createBy, updateBy, lastLoginTime, deleteTime)
    SELECT id AS accountId, loginName, name, password, phoneNo, role, avatar, activated, enabled, remarks, createTime,
           updateTime, createBy, updateBy, lastLoginTime, now()
    FROM account
    WHERE id = old.id;
    RETURN old;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER tr_account_del
    BEFORE DELETE
    ON account
    FOR EACH ROW
EXECUTE PROCEDURE save_canceled_account();


DROP TABLE IF EXISTS account_log CASCADE;
CREATE TABLE account_log
(
    id         SERIAL8     NOT NULL PRIMARY KEY,
    accountId  INT8        NOT NULL,
    ip         VARCHAR(50) NULL,
    action     VARCHAR(20) NOT NULL,
    createTime TIMESTAMP   NOT NULL DEFAULT now()
);


COMMENT ON TABLE account_log IS '用户操作日志(仅用户行为)';
COMMENT ON COLUMN account_log.id IS 'id(自增)';
COMMENT ON COLUMN account_log.accountId IS '帐号id';
COMMENT ON COLUMN account_log.ip IS 'ip(仅登录时)';
COMMENT ON COLUMN account_log.action IS '操作类型';
COMMENT ON COLUMN account_log.createTime IS '创建时间';


-- 项目
DROP TABLE IF EXISTS product CASCADE;
CREATE TABLE product
(
    id          SERIAL8      NOT NULL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    itemNo      VARCHAR(30)  NOT NULL,
    briefCode   VARCHAR(20)  NOT NULL,
    description VARCHAR(500) NULL,
    remarks     VARCHAR(500) NULL,
    activated   BOOLEAN      NOT NULL DEFAULT TRUE,
    createTime  TIMESTAMP    NOT NULL DEFAULT now(),
    updateTime  TIMESTAMP    NOT NULL DEFAULT now(),
    createBy    INT          NOT NULL DEFAULT 0,
    updateBy    INT          NOT NULL DEFAULT 0,
    UNIQUE (itemNo)
);

COMMENT ON TABLE product IS '产品表';
COMMENT ON COLUMN product.id IS 'id(自增)';
COMMENT ON COLUMN product.name IS '项目名';
COMMENT ON COLUMN product.itemNo IS '项目编码(全系统唯一)';
COMMENT ON COLUMN product.briefCode IS '简码(拼音首字母)';
COMMENT ON COLUMN product.remarks IS '备注(仅管理员可见)';
COMMENT ON COLUMN product.activated IS '是否上架中(上架中的产品才可以被加入到套餐中)';
COMMENT ON COLUMN product.createTime IS '记录创建时间';
COMMENT ON COLUMN product.updateTime IS '记录最后一次更新时间';
COMMENT ON COLUMN product.createBy IS '添加帐号的用户';
COMMENT ON COLUMN product.updateBy IS '最近修改帐号的用户';


-- 项目价格
DROP TABLE IF EXISTS product_price CASCADE;
CREATE TABLE product_price
(
    id                SERIAL8   NOT NULL PRIMARY KEY,
    productId         INT8      NOT NULL,
    brandId           INT8      NOT NULL,
    fixedCostPrice    NUMERIC   NOT NULL,
    volatileCostPrice NUMERIC   NOT NULL,
    otherCostPrice    NUMERIC   NOT NULL,
--     fixedCostPrice    VARCHAR(100) NOT NULL ,
--     volatileCostPrice VARCHAR(100) NOT NULL ,
--     otherCostPrice    VARCHAR(100) NOT NULL ,
    marketPrice       NUMERIC   NULL,
    createTime        TIMESTAMP NOT NULL DEFAULT now(),
    updateTime        TIMESTAMP NOT NULL DEFAULT now(),
    createBy          INT       NOT NULL DEFAULT 0,
    updateBy          INT       NOT NULL DEFAULT 0,
    UNIQUE (productId, brandId)
);

COMMENT ON TABLE product_price IS '项目价格表';
COMMENT ON COLUMN product_price.id IS 'id(自增)';
COMMENT ON COLUMN product_price.productId IS '项目id';
COMMENT ON COLUMN product_price.brandId IS '品牌Id';
COMMENT ON COLUMN product_price.fixedCostPrice IS '固定成本价(被加密,(单位元))';
COMMENT ON COLUMN product_price.volatileCostPrice IS '变动成本价(被加密,(单位元))';
COMMENT ON COLUMN product_price.otherCostPrice IS '其它成本价(被加密,(单位元))';
COMMENT ON COLUMN product_price.marketPrice IS '市场价(单位元)';
COMMENT ON COLUMN product_price.createTime IS '记录创建时间';
COMMENT ON COLUMN product_price.updateTime IS '记录最后一次更新时间';
COMMENT ON COLUMN product_price.createBy IS '添加帐号的用户';
COMMENT ON COLUMN product_price.updateBy IS '最近修改帐号的用户';


CREATE INDEX product_name_index ON product(name);
CREATE INDEX product_briefCode_index ON product(briefCode);
CREATE INDEX product_item_no_index ON product(itemNo);

--系统配置
DROP TABLE IF EXISTS config CASCADE;
CREATE TABLE config
(
    id         SERIAL8     NOT NULL PRIMARY KEY,
    type       VARCHAR(20) NOT NULL,
    value      TEXT        NOT NULL,
    valueType  VARCHAR(20) NOT NULL,
    createTime TIMESTAMP   NOT NULL DEFAULT now(),
    createBy   INT         NOT NULL DEFAULT 0,
    updateTime TIMESTAMP   NOT NULL DEFAULT now(),
    updateBy   INT         NOT NULL DEFAULT 0,
    UNIQUE (type)
);

COMMENT ON TABLE config IS '系统配置';
COMMENT ON COLUMN config.id IS 'id';
COMMENT ON COLUMN config.value IS '配置内容';
COMMENT ON COLUMN config.valueType IS '配置内容的值类型';
COMMENT ON COLUMN config.type IS '配置类别(公式/计算下限等)';
COMMENT ON COLUMN config.createTime IS '记录创建时间';
COMMENT ON COLUMN config.createBy IS '记录创建的用户';
COMMENT ON COLUMN config.updateBy IS '最近修改记录的用户';
COMMENT ON COLUMN config.updateTime IS '记录最后一次更新时间';


-- 结果等级配置
DROP TABLE IF EXISTS evaluate_level CASCADE;
CREATE TABLE evaluate_level
(
    id         SERIAL8     NOT NULL PRIMARY KEY,
    levelName  VARCHAR(20) NOT NULL,
    valueFrom  NUMERIC     NULL,
    valueTo    NUMERIC     NULL,
    passed     BOOLEAN     NOT NULL,
    createTime TIMESTAMP   NOT NULL DEFAULT now(),
    createBy   INT         NOT NULL DEFAULT 0,
    updateTime TIMESTAMP   NOT NULL DEFAULT now(),
    updateBy   INT         NOT NULL DEFAULT 0,
    UNIQUE (levelName)
);

CREATE UNIQUE INDEX unique_idx_from ON evaluate_level((valueFrom IS NULL)) WHERE valueFrom IS NULL;
CREATE UNIQUE INDEX unique_idx_to ON evaluate_level((valueTo IS NULL)) WHERE valueTo IS NULL;

COMMENT ON TABLE evaluate_level IS '产品表';
COMMENT ON COLUMN evaluate_level.id IS 'id(自增)';
COMMENT ON COLUMN evaluate_level.levelName IS '等级标签,显示值';
COMMENT ON COLUMN evaluate_level.valueFrom IS '大于等于值(包含)';
COMMENT ON COLUMN evaluate_level.valueTo IS '小于值(不包含)';
COMMENT ON COLUMN evaluate_level.passed IS '是否达标';
COMMENT ON COLUMN evaluate_level.createTime IS '记录创建时间';
COMMENT ON COLUMN evaluate_level.createBy IS '记录创建的用户';
COMMENT ON COLUMN evaluate_level.updateBy IS '最近修改记录的用户';
COMMENT ON COLUMN evaluate_level.updateTime IS '记录最后一次更新时间';

--目前内置正数为达标
INSERT INTO public.evaluate_level (levelname, valuefrom, valueto, passed, createby, updateby)
VALUES ('A', 0, NULL, TRUE, 0, 0);

-- 业务员佣金
DROP TABLE IF EXISTS commission CASCADE;
CREATE TABLE commission
(
    id          SERIAL8     NOT NULL PRIMARY KEY,
    minDiscount NUMERIC     NULL,
    maxDisCount NUMERIC     NULL,
    minAmount   NUMERIC     NULL,
    maxAmount   NUMERIC     NULL,
    discountKey VARCHAR(32) NOT NULL,
    amountKey   VARCHAR(32) NOT NULL,
    --crossKey    varchar(32) NOT NULL,
    brandId     INT8        NOT NULL,
    amount      NUMERIC     NOT NULL,
    createTime  TIMESTAMP   NOT NULL DEFAULT now(),
    createBy    INT         NOT NULL DEFAULT 0,
    updateTime  TIMESTAMP   NOT NULL DEFAULT now(),
    updateBy    INT         NOT NULL DEFAULT 0,
    UNIQUE (discountKey, amountKey, brandId)
);

COMMENT ON TABLE commission IS '业务员佣金交叉表';
COMMENT ON COLUMN commission.id IS 'id(自增)';
COMMENT ON COLUMN commission.minDiscount IS '折扣最低(不包含)';
COMMENT ON COLUMN commission.maxDisCount IS '折扣最高(包含)';
COMMENT ON COLUMN commission.minAmount IS '成交金额最低';
COMMENT ON COLUMN commission.maxAmount IS '成交金额最高';
COMMENT ON COLUMN commission.amount IS '提成率';
COMMENT ON COLUMN commission.discountKey IS '折扣key,用于标识相同的折扣范围';
COMMENT ON COLUMN commission.amountKey IS '订单成交价key,用于标识相同的成交价范围';
--COMMENT ON COLUMN commission.crossKey IS '交叉Key,标识折扣与成交价相交的记录';
COMMENT ON COLUMN commission.brandId IS '品牌Id';
COMMENT ON COLUMN commission.createTime IS '记录创建时间';
COMMENT ON COLUMN commission.createBy IS '记录创建的用户';
COMMENT ON COLUMN commission.updateBy IS '最近修改记录的用户';
COMMENT ON COLUMN commission.updateTime IS '记录最后一次更新时间';


-- 评分历史
DROP TABLE IF EXISTS evaluate_history CASCADE;
CREATE TABLE evaluate_history
(
    id              SERIAL8     NOT NULL PRIMARY KEY,
    productSnapshot TEXT        NULL,
    fixedSum        NUMERIC     NULL,
    volatileSum     NUMERIC     NULL,
    otherSum        NUMERIC     NULL,
    marketSum       NUMERIC     NULL,
    dealPrice       NUMERIC     NULL,
    commissionRate  NUMERIC     NULL,
    evaluateLevel   VARCHAR(20) NULL,
    passed          BOOL        NULL,
    evaluateScore   NUMERIC     NULL,
    createTime      TIMESTAMP   NOT NULL DEFAULT now(),
    accountId       INT         NOT NULL DEFAULT 0
);


COMMENT ON TABLE evaluate_history IS '评分历史';
COMMENT ON COLUMN evaluate_history.id IS 'id(自增)';
COMMENT ON COLUMN evaluate_history.accountId IS '操作人员Id';
COMMENT ON COLUMN evaluate_history.productSnapshot IS '提交评估的项目快照';
COMMENT ON COLUMN evaluate_history.fixedSum IS '固定成本价合计';
COMMENT ON COLUMN evaluate_history.volatileSum IS '可变成本价合计';
COMMENT ON COLUMN evaluate_history.otherSum IS '其它成本价合计';
COMMENT ON COLUMN evaluate_history.marketSum IS '市场价(原价)合计';
COMMENT ON COLUMN evaluate_history.dealPrice IS '成交价';
COMMENT ON COLUMN evaluate_history.commissionRate IS '销售提成率';
COMMENT ON COLUMN evaluate_history.evaluateLevel IS '评价等级';
COMMENT ON COLUMN evaluate_history.passed IS '是否达标';
COMMENT ON COLUMN evaluate_history.evaluateScore IS '评价分数';
COMMENT ON COLUMN evaluate_history.createTime IS '记录创建时间';


-- 登录日志
DROP TABLE IF EXISTS login_log CASCADE;
CREATE TABLE login_log
(
    id         SERIAL8     NOT NULL PRIMARY KEY,
    accountId  INT8        NULL,
    type       VARCHAR(20) NOT NULL DEFAULT 'login',
    ip         VARCHAR(50) NULL,
    createTime TIMESTAMP   NOT NULL DEFAULT now()
);

COMMENT ON TABLE login_log IS '操作日志';
COMMENT ON COLUMN login_log.id IS 'id(自增)';
COMMENT ON COLUMN login_log.accountId IS '操作人员Id';
COMMENT ON COLUMN login_log.type IS '类型(登录/登出)';
COMMENT ON COLUMN login_log.ip IS '登录ip';
COMMENT ON COLUMN login_log.createTime IS '记录创建时间';


-- 操作日志
DROP TABLE IF EXISTS operation_log CASCADE;
CREATE TABLE operation_log
(
    id            SERIAL8      NOT NULL PRIMARY KEY,
    operationName VARCHAR(50)  NOT NULL,
    accountId     INT8         NOT NULL,
    result        VARCHAR(20)  NOT NULL,
    message       VARCHAR(500) NULL,
    detail        TEXT         NULL,
    createTime    TIMESTAMP    NOT NULL DEFAULT now()
);


COMMENT ON TABLE operation_log IS '操作日志';
COMMENT ON COLUMN operation_log.id IS 'id(自增)';
COMMENT ON COLUMN operation_log.operationName IS '操作名';
COMMENT ON COLUMN operation_log.accountId IS '操作人员Id';
COMMENT ON COLUMN operation_log.result IS '操作结果';
COMMENT ON COLUMN operation_log.message IS '如果失败,失败原因描述';
COMMENT ON COLUMN operation_log.detail IS '操作详细';
COMMENT ON COLUMN operation_log.createTime IS '记录创建时间';


-- 品牌
DROP TABLE IF EXISTS brand CASCADE;
CREATE TABLE brand
(
    id   SERIAL8     NOT NULL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    logo VARCHAR(50) NULL
);

COMMENT ON TABLE brand IS '品牌';
COMMENT ON COLUMN brand.id IS 'id(自增)';
COMMENT ON COLUMN brand.name IS '品牌名';
COMMENT ON COLUMN brand.logo IS 'logo';


INSERT INTO brand (id, name, logo) VALUES (1, '美年', '/brands/meinian.png');
INSERT INTO brand (id, name, logo) VALUES (2, '美兆', '/brands/meizhao.png');
INSERT INTO brand (id, name, logo) VALUES (1, '慈铭', '/brands/ciming.png');
INSERT INTO brand (id, name, logo) VALUES (1, '奥亚', '/brands/aoya.png');